/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.home.dao;

import com.home.common.Constants;
import com.home.common.Role;
import com.home.common.utils.HibernateUtil;
import com.home.mapping.Account;
import com.home.mapping.Agent;
import com.home.mapping.AttributeGroup;
import com.home.mapping.Scorecard;
import com.home.mapping.Team;
import com.home.mapping.Venture;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;

/**
 *
 * @author pnduy
 */
public class AgentDao extends BaseDao{
    
    /**
     * find agent by id
     * @param id
     * @return 
     */
    public Agent findAgentById(Integer id) {
        Agent agent = new Agent();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Criteria criteria = session.createCriteria(Agent.class);
            criteria.add(Restrictions.eq("id_agent",id));
            
            List result = criteria.list();
            if (result.size() > 0) {
                agent = (Agent)result.get(0);
            }
        } catch (Exception e) {
        } finally {
            session.close();
        }
        return agent;
    }
    
    public Agent findAgentByIdSQL(Integer id) {
        Agent agent = new Agent();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT * FROM agent ag WHERE ag.id_agent = :id").addEntity(Agent.class).setParameter("id", id);
            
            
            if (query.list().size() > 0) {
                agent = (Agent)query.list().get(0);
            }
        } catch (Exception e) {
        } finally {
            session.close();
        }
        return agent;
    }
    
    public List<Agent> findAgentByVenture(Integer idVenture) {
        List<Agent> listAgent = new ArrayList<Agent>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT " +
                                                " * FROM agent " +                                                
                                                "LEFT JOIN team ON agent.fk_team = team.id_team " +
                                                "LEFT JOIN venture ON team.fk_venture = venture.id_venture " +
                                                "WHERE venture.id_venture = :venture ORDER BY agent.agent_name ASC")
                                                .addEntity(Agent.class)                                                                                              
                                                .setParameter("venture", idVenture);
            
            listAgent = query.list();
            
        } catch (Exception e) {
        } finally {
            session.close();
        }
        return listAgent;
    }
    
    public Map<Integer,Agent> findAgentByIds(List<Integer> listAgentIds) {
        Map<Integer,Agent> map = new HashMap<Integer,Agent>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT * FROM scorecard WHERE "
                    + "scorecard.fk_agent_AGENT IN ("+StringUtils.join(listAgentIds, ",")+")")
                    .addEntity(Agent.class);
                    
            List list = query.list();
            for (Object obj : list) {
                Agent agent = (Agent)obj;
                map.put(agent.getIdAgent(), agent);
            }
            
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return map;
    }
    
    public List<Agent> findAgentsByRole(int role) {
        List<Agent> listAgent = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT agent.* FROM agent "+                    
                                                "WHERE agent.role = :role")
                                                .addEntity(Agent.class)                                              
                                                .setParameter("role", role);           
                                                
            listAgent = query.list();
            
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        return listAgent;
    }
    
    public List<Agent> findQAByVenture(int venture) {
        List<Agent> listAgent = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT " +
                                                " * FROM agent " +                                                
                                                "LEFT JOIN team ON agent.fk_team = team.id_team " +
                                                "LEFT JOIN venture ON team.fk_venture = venture.id_venture " +
                                                "WHERE agent.role = :role AND venture.id_venture = :venture ORDER BY agent.agent_name ASC")
                                                .addEntity(Agent.class)                                              
                                                .setParameter("role", Role.ROLE_QA)
                                                .setParameter("venture", venture);                                                
            listAgent = query.list();
            
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        return listAgent;
    }
    
    public List<Agent> findAllQA() {
        List<Agent> listAgent = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT " +
                                                " * FROM agent " +                                                
                                                "LEFT JOIN team ON agent.fk_team = team.id_team " +
                                                "LEFT JOIN venture ON team.fk_venture = venture.id_venture " +
                                                "WHERE agent.role = :role")
                                                .addEntity(Agent.class)                                              
                                                .setParameter("role", Role.ROLE_QA);
                                                
            listAgent = query.list();
            
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        return listAgent;
    }
    
    public List<Agent> findAgentAndVenture() {
        List<Agent> listAgent = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT * FROM agent").addEntity(Agent.class);
            listAgent = query.list();
            for (Agent ag : listAgent) {
                Hibernate.initialize(ag.getTeam().getVenture());
            }
        } catch (Exception e) {
        } finally {
            session.close();
        }
        return listAgent;
    }
    
    public List<Agent> findTeamLeadByVenture(int venture) {
        List<Agent> listAgent = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT " +
                                                " * FROM agent " +                                                
                                                "LEFT JOIN team ON agent.fk_team = team.id_team " +
                                                "LEFT JOIN venture ON team.fk_venture = venture.id_venture " +
                                                "WHERE agent.role = :role AND venture.id_venture = :venture ORDER BY agent.agent_name ASC")
                                                .addEntity(Agent.class)                                              
                                                .setParameter("role", Role.ROLE_TEAM_LEAD)
                                                .setParameter("venture", venture);                                                
            listAgent = query.list();
            
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        return listAgent;
    }
}
